﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Domain;

namespace Agresso.Interface.Layer.SQL
{
    public class UserSQL
    {
        public static string GetAllUser()
        {
            string sql = "select * from [i_user_master]";
            return sql;
        }
        public static string GetMaxUserID()
        {
            return @"SELECT MAX(user_id) FROM [i_user_master]";
        }
        public static string GetUserByCode()
        {
            string sql = "select TOP 1 * from [i_user_master] WHERE user_code = @usercode";
            return sql;
        }
        public static string GetUserByID()
        {
            string sql = "select TOP 1 * from [i_user_master] WHERE user_id = @userid";
            return sql;
        }
        public static string InsertUser()
        {
            string sql = @"INSERT INTO [i_user_master]
           ([user_code]
           ,[user_name]
           ,[password]
           ,[expiry_date]
           ,[active_code]
           ,[email_id]
           ,[cretn_date]
           ,[cretn_by]
           ,[modfctn_date]
           ,[modfctn_by]
           ,[modfctn_termnl]            
            ,[password_expired]
            ,[password_expiry_days]
            ,m_softmarAccess
            ,m_crewpayrollAccess
            ,m_winsdsAccess
            ,m_dadeskAccess
            ,m_adminAccess
            ,m_sendAlertToEmail
            ,m_alert
            )
     VALUES
           (@usercode
           ,@username
           ,@password
           ,@expirydate
           ,@activecode
           ,@emailid
           ,@createndate
           ,@createnby
           ,@modificationdate
           ,@modificationby
           ,@modificationterminal
            , @passwordexpired
            ,@passwordexpirydays
             ,@m_softmarAccess
            ,@m_crewpayrollAccess
            ,@m_winsdsAccess
            ,@m_dadeskAccess
            ,@m_adminAccess
            ,@m_sendAlertToEmail
            ,@m_alert
            )";
            return sql;
        }
        public static string UpdateUser()
        {
            string sql = @"UPDATE [i_user_master]
   SET [user_code] = @usercode
      ,[user_name] = @username
      ,[password] = @password
      ,[expiry_date] = @expirydate
      ,[active_code] = @activecode
      ,[email_id] = @emailid
      ,[modfctn_date] =@modificationdate
      ,[modfctn_by] = @modificationby
      ,[modfctn_termnl] = @modificationterminal
       ,[password_expired]=@passwordexpired
        ,m_softmarAccess=@m_softmarAccess
            ,m_crewpayrollAccess=@m_crewpayrollAccess
            ,m_winsdsAccess=@m_winsdsAccess
            ,m_dadeskAccess=@m_dadeskAccess
            ,m_adminAccess=@m_adminAccess
            ,m_sendAlertToEmail= @m_sendAlertToEmail
            ,m_alert = @m_alert
 WHERE user_id = @userid";
            return sql;
        }
        public static string UpdatePasswordUser()
        {
            string sql = @"UPDATE [i_user_master]
   SET [password] = @password     
      ,[modfctn_date] =@modificationdate
      ,[modfctn_by] = @modificationby
      ,[modfctn_termnl] = @modificationterminal
      ,[expiry_date]=@expirydate
 WHERE user_id = @userid";
            return sql;
        }
        public static string DeleteUser()
        {
            string sql = @"DELETE FROM [i_user_master]
 WHERE user_id = @userid";
            return sql;
        }
        public static string InsertLogUser()
        {
            return @"INSERT INTO [i_user_login_history]
           ([user_id]
           ,[log_in_date]
           ,[session_code]
           ,[remark])
     VALUES
           (@userid, @logindate, @sessioncode, @remark)";
        }
        public static string UpdateLogoutUser()
        {
            return @"UPDATE [i_user_login_history]
   SET [log_out_date] = @logoutdate     
 WHERE session_code = @sessioncode";
        }
        public static string CountUserLog()
        {
            return @"SELECT COUNT(*)
  FROM [i_user_login_history] where session_code = @sessioncode";
        }
        public static string InsertRoleMaster()
        {
            return @"INSERT INTO [i_role_master]
           ([role_master_name]
           ,[cretn_date]
           ,[cretn_by]
           ,[modfctn_date]
           ,[modfctn_by])
     VALUES
           (@rolemastername
           ,@cretndate
           ,@cretnby
           ,@modfctndate
           ,@modfctnby)";
        }
        public static string GetMaxRoleMasterID()
        {
            return @"SELECT MAX(role_master_id) FROM [i_role_master]";
        }
        public static string GetRoleMasterByID()
        {
            return @"SELECT * FROM [i_role_master] where role_master_id = @rolemasterid";
        }
        public static string GetAllRoleMaster()
        {
            return @"SELECT * FROM [i_role_master]";
        }
        public static string UpdateRoleMaster()
        {
            return @"UPDATE [i_role_master]
   SET [role_master_name] = @rolemastername
      ,[modfctn_date] = @modfctndate
      ,[modfctn_by] = @modfctnby
 WHERE role_master_id = @rolemasterid";
        }
        public static string DeleteRoleMaster()
        {
            return @"DELETE FROM [i_role_master]
      WHERE  [role_master_id] = @rolemasterid";
        }
        public static string InsertRoleDetil()
        {
            return @"INSERT INTO [i_role_detail]
           ([role_master_id]
           ,[view_flag]
           ,[new_flag]
           ,[update_flag]
            , [form_name]
            ,[delete_flag])
     VALUES
           (@rolemasterid
           ,@viewflag
           ,@newflag
           ,@updateflag
            ,@formname
            ,@deleteflag
            )";
        }
        public static string DeleteRoleDetilByRoleMaster()
        {
            return @"DELETE FROM [i_role_detail]
      WHERE [role_master_id] = @rolemasterid";
        }

        public static string GetRoleDetilByRoleMaster()
        {
            return @"SELECT * FROM [i_role_detail]
      WHERE [role_master_id] = @rolemasterid";
        }
        public static string SearchUserByCodeName()
        {
            string sql = "select * from [i_user_master] WHERE user_code like '%'+@usercode+'%' or user_name like '%'+@usercode+'%'";
            return sql;
        }

        public static string SearchRoleMasterByCodeName()
        {
            string sql = "select * from [i_role_master] WHERE role_master_name like '%'+@name+'%'";
            return sql;
        }

        public static string InsertUserRole()
        {
            return @"INSERT INTO [i_user_role]
           ([user_id]
           ,[role_master_id])
     VALUES
           (@userid
           ,@rolemasterid)";
        }
        public static string DeleteUserRoleByUserID()
        {
            return @"DELETE FROM [i_user_role]
      WHERE [user_id] = @userid";
        }
        public static string GetUserRoleByUserID()
        {
            return @"SELECT * FROM [i_user_role]
      WHERE [user_id] = @userid";
        }
    }
}
